/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlgenerators.search;

import entities.Search;
import entities.SearchLanguageInfoRequerimentAndOperator;

/**
 *
 * @author damfercer
 */
public class LanguageAndResitrictionsSQLGenerator implements SearchSqlGeneratorInterface{
    private String res;
    @Override
    public String getSqlFromSearch(Search search) {
        res="";
        if(!search.getSearchLanguageInfoRequerimentAndOperatorList().isEmpty()){
            res+=" AND (";
            int i=0;
            for(SearchLanguageInfoRequerimentAndOperator restriction: search.getSearchLanguageInfoRequerimentAndOperatorList()){
                if(i==0){
                    res+="TRUE";
                    res+=generateSearchLanguageInfoRequerimentAndOperatorSQL(restriction);
                }
                else{
                    res+=generateSearchLanguageInfoRequerimentAndOperatorSQL(restriction);
                }
                i++;
            }
            res+=")";
        }
        return res;
    }
    
    private String generateSearchLanguageInfoRequerimentAndOperatorSQL(SearchLanguageInfoRequerimentAndOperator restr){
        String result="";
        boolean videoRestriction=false;
        if(restr.getRequiredVideo()!=null){
            videoRestriction=restr.getRequiredVideo();
        }
        if(restr.getLanguage()!=null || restr.getMinimumLanguageLevel()!=null || videoRestriction){
            result=" AND EXISTS(SELECT ID_CANDIDATE_LANGUAGE_INFO FROM candidate_language_info WHERE candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE AND candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL";
            if(restr.getLanguage()!=null){
                result+=" AND candidate_language_info.LANGUAGE="+restr.getLanguage().getIdLanguage();
            }
            if(restr.getMinimumLanguageLevel()!=null){
                result+=" AND language_level.VALUE>="+restr.getMinimumLanguageLevel().getValue();
            }
            if(videoRestriction){
                result+=" AND candidate_language_info.VIDEO IS NOT NULL";
            }
            result+=")";
        }
        return result;
    }
}
